# encoding: utf-8
from sqlalchemy import Column, String, create_engine, Integer, MetaData, Table, desc
from sqlalchemy.orm import sessionmaker, scoped_session
from sqlalchemy.ext.declarative import declarative_base
import time
import threading
import json
import datetime
import base64
import uuid

Base = declarative_base()

# 初始化数据库连接:
engine = create_engine(
    'sqlite:///police.db')


# def __repr__(self):
# 	return "<User(name='%s', fullname='%s', password='%s')>" % (
# 			self.name, self.fullname, self.password)

class FaceRecord(Base):
    # 表的名字:
    __tablename__ = 't_b_face_record'

    # 表的结构:
    FR_ID = Column(Integer, primary_key=True, autoincrement=True)
    FR_IMAGEURL = Column(String(500))
    FR_DEVICE_NUMBER = Column(Integer)
    FR_BIG_IMAGEURL = Column(String(500))
    FR_LAT = Column(String(100))
    FR_LNG = Column(String(100))
    FR_LAT_NS = Column(String(100))
    FR_LNG_EW = Column(String(100))
    FR_IMAGE_ISUPLOAD = Column(Integer)
    ISDEL = Column(Integer)
    CREATOR = Column(String(100))
    CREATE_TIME = Column(String(100))
    EDITOR = Column(String(100))
    EDIT_TIME = Column(String(100))
    UUID = Column(String(100))



class FaceBlack(Base):
    # 表的名字:
    __tablename__ = 't_b_face_black'

    # 表的结构:
    FB_ID = Column(Integer, primary_key=True, autoincrement=True)
    FB_IMAGEURL = Column(String(500))
    FB_NAME = Column(String(100))
    FB_AGE = Column(Integer)
    FB_SEX = Column(Integer)
    FB_ZJ_NUM = Column(String(100))
    FB_PHONE = Column(String(100))
    FB_NOTE = Column(String(100))
    ISDEL = Column(Integer)
    CREATOR = Column(Integer)
    CREATE_TIME = Column(String(100))
    EDITOR = Column(Integer)
    EDIT_TIME = Column(String(100))


class FaceWarning(Base):
    # 表的名字:
    __tablename__ = 't_b_face_warning'

    # 表的结构:
    FW_ID = Column(Integer, primary_key=True, autoincrement=True)
    FW_FRID = Column(Integer)
    FW_FBID = Column(Integer)
    ISDEL = Column(Integer)
    CREATOR = Column(Integer)
    CREATE_TIME = Column(String(100))
    IS_UPLOAD = Column(Integer)
    EDITOR = Column(Integer)
    EDIT_TIME = Column(String(100))


class PlateRecord(Base):
    # 表的名字:
    __tablename__ = 't_b_plate_record'

    # 表的结构:
    PR_ID = Column(Integer, primary_key=True, autoincrement=True)
    PR_IMAGEURL = Column(String(500))
    PR_BIG_IMAGEURL = Column(String(500))
    PR_NUMBER = Column(String(500))
    PR_COLOR = Column(String(100))
    PR_LOGO = Column(String(100))
    PR_DEVICE_NUMBER = Column(String(100))
    PR_LAT = Column(String(100))
    PR_LNG = Column(String(100))
    PR_LAT_NS = Column(String(100))
    PR_LNG_EW = Column(String(100))
    PR_IMAGE_ISUPLOAD = Column(Integer)
    ISDEL = Column(Integer)
    CREATOR = Column(String(100))
    CREATE_TIME = Column(String(100))
    EDITOR = Column(String(100))
    EDIT_TIME = Column(String(100))
    UUID = Column(String(100))


class PlateBlack(Base):
    # 表的名字:
    __tablename__ = 't_b_plate_black'

    # 表的结构:
    PB_ID = Column(Integer, primary_key=True, autoincrement=True)
    PB_NUMBER = Column(String(500))
    PB_LOGO = Column(String(100))
    PB_COLOR = Column(String(100))
    PB_OWNER_NAME = Column(String(100))
    PB_OWNER_PHONE = Column(String(100))
    PB_NOTE = Column(String(100))
    ISDEL = Column(Integer)
    CREATOR = Column(Integer)
    CREATE_TIME = Column(String(100))
    EDITOR = Column(Integer)
    EDIT_TIME = Column(String(100))


class PlateWarning(Base):
    # 表的名字:
    __tablename__ = 't_b_plate_warning'

    # 表的结构:
    PW_ID = Column(Integer, primary_key=True, autoincrement=True)
    PW_PRID = Column(Integer)
    PW_PBID = Column(Integer)
    ISDEL = Column(Integer)
    CREATOR = Column(Integer)
    CREATE_TIME = Column(String(100))
    IS_UPLOAD = Column(Integer)
    EDITOR = Column(Integer)
    EDIT_TIME = Column(String(100))


class Device(Base):
    # 表的名字:
    __tablename__ = 't_b_device'

    # 表的结构:
    DEVICE_ID = Column(Integer, primary_key=True, autoincrement=True)
    DEVICE_NUMBER = Column(String(100))
    DEVICE_NAME = Column(String(100))
    DEVICE_RTSPURL = Column(String(100))
    DEVICE_RTSPURL_SUB = Column(String(100))
    DEVICE_TYPE = Column(String(100))
    DEVICE_STATE = Column(String(100))
    ISDEL = Column(Integer)


# 创建DBSession类型:
session_factory = sessionmaker(bind=engine)
DBSession = scoped_session(session_factory)
###之前上面两段代码中出现过的东西就不再声明了###
# 这就是为什么表类一定要继承Base，因为Base会通过一些方法来通过引擎初始化数据库结构。不继承Base自然就没有办法和数据库发生联系了。
Base.metadata.create_all(engine)
session = DBSession()


class FaceRecordThread(threading.Thread):
    def selectLastImg(self, deviceNum, num):
        session = DBSession()
        if deviceNum != None and deviceNum != '':
            result = session.query(FaceRecord).filter_by(FR_DEVICE_NUMBER = deviceNum).order_by(FaceRecord.CREATE_TIME.desc()).limit(num).offset(1)
        else:
            result = session.query(FaceRecord).order_by(FaceRecord.CREATE_TIME.desc()).limit(num).offset(1)
        arry = []
        for row in result:
            face = {}
            face["smallImage"] = row.FR_IMAGEURL
            face["bigImage"] = row.FR_BIG_IMAGEURL
            face["deviceNumber"] = row.FR_DEVICE_NUMBER
            arry.append(face)
        session.close()
        return arry

    def insert(self, imageUrl, bigImageUrl, deviceId,gpsData):
        session = DBSession()
        new_state = FaceRecord(
            FR_IMAGEURL=imageUrl, 
            FR_BIG_IMAGEURL=bigImageUrl, 
            FR_DEVICE_NUMBER=deviceId,
            CREATE_TIME=getNowTime(),
            FR_LAT=str(gpsData['lat']),
            FR_LNG=str(gpsData['lon']),
            FR_LAT_NS=str(gpsData['lat_ns']),
            FR_LNG_EW=str(gpsData['lon_ew']),
            FR_IMAGE_ISUPLOAD=0,
            ISDEL=0,
            UUID=getUUID())
        session.add(new_state)
        session.commit()
        session.close()

    def selectFaceById(self, image):
        session = DBSession()
        data = session.query(FaceRecord).filter_by(FR_IMAGEURL=image).first()
        session.close()
        return data
    def selectUpload(self):
        session = DBSession()
        bigData = session.query(FaceRecord).filter_by(FR_IMAGE_ISUPLOAD=0).group_by(FaceRecord.FR_BIG_IMAGEURL).order_by(FaceRecord.CREATE_TIME.asc()).first()
        data = session.query(FaceRecord).filter_by(FR_BIG_IMAGEURL=bigData.FR_BIG_IMAGEURL).all()

        result = {}
        result['list'] = []
        result['frBigImageUrl'] = bigData.FR_BIG_IMAGEURL
        result['deviceNumber'] = bigData.FR_DEVICE_NUMBER
        for row in data:
            imageData = {}
            imageData['frImageUrl'] = row.FR_IMAGEURL
            imageData['uuid'] = row.UUID
            imageData['lat'] = row.FR_LAT
            imageData['lng'] = row.FR_LNG
            result['list'].append(imageData)
        session.close()
        return result
    def updataUpload(self,imageUrl):
        session = DBSession()
        jcState = session.query(FaceRecord).filter_by(FR_BIG_IMAGEURL=imageUrl).first()
        jcState.FR_IMAGE_ISUPLOAD = 1
        session.commit()
        session.close()  

class PlateRecordThread(threading.Thread):
    def insertPlateInfo(self, smallImage, bigImage, plateNum, deviceNum, gpsData):
        session = DBSession()
        plateRecord = PlateRecord(PR_IMAGEURL=smallImage,
                        PR_BIG_IMAGEURL=bigImage,
                        PR_NUMBER=plateNum,
                        PR_DEVICE_NUMBER=deviceNum,
                        PR_LAT=str(gpsData['lat']),
                        PR_LNG=str(gpsData['lon']),
                        PR_LAT_NS=str(gpsData['lat_ns']),
                        PR_LNG_EW=str(gpsData['lon_ew']),
                        PR_IMAGE_ISUPLOAD=0,
                        CREATE_TIME=getNowTime(),
                        UUID=getUUID(),
                        ISDEL=0)
        session.add(plateRecord)
        session.commit()
        session.close()

    def selectPlateById(self, image):
        session = DBSession()
        data = session.query(PlateRecord).filter_by(PR_IMAGEURL=image).first()
        return data.PR_ID

    def selectLastImg(self, deviceNum, num):
        session = DBSession()
        if deviceNum != None and deviceNum != '':
            result = session.query(PlateRecord).filter_by(PR_DEVICE_NUMBER = deviceNum).order_by(PlateRecord.CREATE_TIME.desc()).limit(num).offset(1)
        else:
            result = session.query(PlateRecord).order_by(PlateRecord.CREATE_TIME.desc()).limit(num).offset(1)
        arry = []
        for row in result:
            plate = {}
            plate["smallImage"] = row.PR_IMAGEURL
            plate["bigImage"] = row.PR_BIG_IMAGEURL
            plate["deviceNumber"] = row.PR_DEVICE_NUMBER
            arry.append(plate)
        session.close()
        return arry
    def selectUpload(self):
        session = DBSession()
        plateRecord = session.query(PlateRecord).filter_by(PR_IMAGE_ISUPLOAD=0).order_by(PlateRecord.CREATE_TIME.asc()).first()


        imageData = {}
        imageData['prImageUrl'] = plateRecord.PR_IMAGEURL
        imageData['prBigImageUrl'] = plateRecord.PR_BIG_IMAGEURL
        imageData['uuid'] = plateRecord.UUID
        imageData['lat'] = plateRecord.PR_LAT
        imageData['lng'] = plateRecord.PR_LNG
        imageData['cpNO'] = plateRecord.PR_NUMBER.encode('utf-8')
        imageData['deviceNumber'] = plateRecord.PR_DEVICE_NUMBER
        session.close()
        return imageData
    def updataUpload(self,imageUrl):
        session = DBSession()
        jcState = session.query(PlateRecord).filter_by(PR_IMAGEURL=imageUrl).first()
        jcState.PR_IMAGE_ISUPLOAD = 1
        session.commit()
        session.close()  


class FaceWarningThread(threading.Thread):
    def insert(self, frid, rbid):
        session = DBSession()
        new_state = FaceWarning(FW_FRID=frid, FW_FBID=rbid ,CREATE_TIME=getNowTime(),IS_UPLOAD=0,ISDEL=0)
        session.add(new_state)
        session.commit()
        session.close()

    def select(self, startTime, endTime):
        session = DBSession()
        result = session.query(FaceWarning.CREATE_TIME,
                               FaceRecord.FR_IMAGEURL,
                               FaceRecord.FR_BIG_IMAGEURL,
                               FaceRecord.FR_DEVICE_NUMBER,
                               FaceBlack.FB_NAME,
                               FaceBlack.FB_AGE,
                               FaceBlack.FB_SEX,
                               FaceBlack.FB_ZJ_NUM,
                               FaceBlack.FB_IMAGEURL,
                               FaceBlack.FB_PHONE,
                               FaceBlack.FB_NOTE, Device.DEVICE_NAME).filter(FaceWarning.FW_FRID == FaceRecord.FR_ID,
                                                                             FaceWarning.FW_FBID == FaceBlack.FB_ID,
                                                                             FaceWarning.CREATE_TIME.between(startTime,
                                                                                                             endTime),
                                                                             Device.DEVICE_NUMBER == FaceRecord.FR_DEVICE_NUMBER).order_by(
            FaceWarning.CREATE_TIME.desc()).limit(5)
        arr = []
        for row in result:
            face = {}
            face['createTime'] = row.CREATE_TIME
            face['frImageUrl'] = row.FR_IMAGEURL
            face['frBigImageUrl'] = row.FR_BIG_IMAGEURL
            face['frDeviceNumber'] = row.FR_DEVICE_NUMBER
            face['fbName'] = row.FB_NAME
            face['fbAge'] = row.FB_AGE
            face['fbSex'] = row.FB_SEX
            face['fbZjNum'] = row.FB_ZJ_NUM
            face['fbImageUrl'] = row.FB_IMAGEURL
            face['fbNote'] = row.FB_NOTE
            face['fbPhone'] = row.FB_PHONE
            face['fDeviceName'] = row.DEVICE_NAME
            arr.append(face)
        print arr
        session.close()
        return arr

    def selectUpload(self):
        session = DBSession()
        imageData = {}
        faceWarning = session.query(FaceWarning).filter_by(IS_UPLOAD=0).order_by(FaceWarning.CREATE_TIME.asc()).first()
        if faceWarning != None:
            faceRecord = session.query(FaceRecord).filter_by(FR_ID=faceWarning.FW_FRID).first()
            faceBlack = session.query(FaceBlack).filter_by(FB_ID=faceWarning.FW_FBID).first()
            imageData['recordId'] = faceRecord.UUID
            imageData['faceBlackImageUrl'] = faceBlack.FB_IMAGEURL
            imageData['smallImage'] = faceRecord.FR_IMAGEURL
            imageData['deviceNumber'] = faceRecord.FR_DEVICE_NUMBER
            imageData['id'] = faceWarning.FW_ID
            session.close()
        return imageData
    def updataUpload(self,imageUrl):
        session = DBSession()
        jcState = session.query(FaceWarning).filter_by(FW_ID=imageUrl).first()
        jcState.IS_UPLOAD = 1
        session.commit()
        session.close()  

class PlateWarningThread(threading.Thread):

    def insert(self, prid, pbid):
        session = DBSession()
        new_state = PlateWarning(PW_PRID=prid, PW_PBID=pbid,CREATE_TIME=getNowTime(),IS_UPLOAD=0,ISDEL=0)
        session.add(new_state)
        session.commit()
        session.close()

    def select(self, startTime, endTime):
        session = DBSession()
        result = session.query(PlateWarning.CREATE_TIME,
                               PlateRecord.PR_IMAGEURL,
                               PlateRecord.PR_BIG_IMAGEURL,
                               PlateRecord.PR_NUMBER,
                               PlateRecord.PR_COLOR,
                               PlateRecord.PR_DEVICE_NUMBER,
                               PlateRecord.PR_LOGO,
                               PlateBlack.PB_NOTE,
                               Device.DEVICE_NAME).filter(PlateWarning.PW_PRID == PlateRecord.PR_ID,
                                                          PlateWarning.PW_PBID == PlateBlack.PB_ID,
                                                          PlateWarning.CREATE_TIME.between(startTime, endTime),
                                                          Device.DEVICE_NUMBER == PlateRecord.PR_DEVICE_NUMBER).order_by(
            PlateWarning.CREATE_TIME.desc()).limit(5)

        arry = []
        for row in result:
            plate = {}
            plate["createTime"] = row.CREATE_TIME
            plate["prImageUrl"] = row.PR_IMAGEURL
            plate["prBigImageUrl"] = row.PR_BIG_IMAGEURL
            plate["prNumber"] = row.PR_NUMBER
            plate["prColor"] = row.PR_COLOR
            plate["prDeviceNumber"] = row.PR_DEVICE_NUMBER
            plate["prLogo"] = row.PR_LOGO
            plate["pbNote"] = row.PB_NOTE
            plate["pDeviceName"] = row.DEVICE_NAME
            arry.append(plate)
        # print('-----------------------------')
        print(json.dumps(arry))
        # print('-----------------------------')
        session.close()
        return arry
    def selectUpload(self):
        session = DBSession()
        imageData = {}
        plateWarning = session.query(PlateWarning).filter_by(IS_UPLOAD=0).order_by(PlateWarning.CREATE_TIME.asc()).first()
        if plateWarning != None:
            plateRecord = session.query(PlateRecord).filter_by(PR_ID=plateWarning.PW_PRID).first()
            imageData['recordId'] = plateRecord.UUID
            imageData['cpNO'] = plateRecord.PR_NUMBER.encode('utf-8')
            imageData['smallImage'] = plateRecord.PR_IMAGEURL
            imageData['deviceNumber'] = plateRecord.PR_DEVICE_NUMBER
            imageData['id'] = plateWarning.PW_ID
            session.close()
        return imageData   
    def updataUpload(self,imageUrl):
        session = DBSession()
        jcState = session.query(PlateWarning).filter_by(PW_ID=imageUrl).first()
        jcState.IS_UPLOAD = 1
        session.commit()
        session.close()  


class FaceBlackThread(threading.Thread):

    def selectById(self,fbZjNum):
        session = DBSession()   
        data = session.query(FaceBlack).filter_by(FB_ZJ_NUM=fbZjNum).first()
        resultData = {}
        resultData['fbId'] = data.FB_ID
        resultData['fbImageUrl'] = data.FB_IMAGEURL
        resultData['fbName'] = data.FB_NAME
        return resultData
    def insertFaceBlack(self, faceList):
        session = DBSession()
        session.query(FaceBlack).delete(synchronize_session=False)
        for face in faceList:
            age = 0
            name = unicode(base64.decodestring(face['fbName']), "utf-8")
            # name = base64.b64decode()
            if ('fbAge' in face) :
                age = face['fbAge']
            session.add(FaceBlack(FB_IMAGEURL=face['fbImageurl'],FB_NAME=name,
                 FB_AGE=age, FB_SEX=face['fbSex'], FB_ZJ_NUM=face['fbZjNum'],
                 FB_PHONE=face['fbPhone'],FB_NOTE=face['fbNote'], ISDEL=0))
        session.commit()
        session.close()

    def selectImagesByZjnum(self,fbZjNum):
        session = DBSession() 
        dataList = session.query(FaceBlack).filter_by(FB_ZJ_NUM=fbZjNum).all()
        imgList = []
        for data in dataList:
            imgList.append(data.FB_IMAGEURL)
        return imgList


class PlateBlackThread(threading.Thread):
    def selectById(self, pbNum):
        session = DBSession()
        data = session.query(PlateBlack).filter_by(PB_NUMBER=pbNum.encode('utf-8')).first()
        session.close()
        return data

    def selectPlateByNum(self, plateNum):
        session = DBSession()
        plateBlack = session.query(PlateBlack).filter_by(PB_NUMBER=plateNum.encode('utf-8')).count()
        # if plateBlack != None:
        #     return plateBlack
        # else:
        session.close()
        return plateBlack
        # plateBlack = session.query.filter(PlateBlack.PB_NUMBER=plateNum).count()

    def insertPlateBlack(self, plateList):
        session = DBSession()
        session.query(PlateBlack).delete(synchronize_session=False)
        for plate in plateList:
            plateNum = unicode(base64.decodestring(plate['vbNumber']), "utf-8")
            logo = unicode(base64.decodestring(plate['vbLogo']), "utf-8")
            session.add(PlateBlack(PB_NUMBER=plateNum,PB_LOGO=logo,
                 PB_COLOR=plate['vbColor'], PB_OWNER_NAME=plate['vbOwnerName'], PB_OWNER_PHONE=plate['vbOwnerPhone'],
                 PB_NOTE=plate['vbNote'], ISDEL=0))
        session.commit()
        session.close()


class DeviceThread(threading.Thread):
    def selectAll(self):
        session = DBSession()
        sql = '''SELECT 
            tbd.DEVICE_ID,
            tbd.DEVICE_NAME,
            tbd.DEVICE_NUMBER,
            tbd.DEVICE_RTSPURL,
            tbd.DEVICE_RTSPURL_SUB,
            tbd.DEVICE_STATE,
            tbd.DEVICE_TYPE 
            FROM t_b_device tbd 
            WHERE tbd.ISDEL = 0 
            ORDER BY tbd.DEVICE_ID asc'''

        result = session.execute(sql)
        arry = []
        for row in result:
            device = {}
            device["deviceId"] = row[0]
            device["deviceName"] = row[1]
            device["deviceNumber"] = row[2]
            device["deviceRtspUrl"] = row[3]
            device["deviceRtspUrlSub"] = row[4]
            device["deviceState"] = row[5]
            device["deviceType"] = row[6]
            arry.append(device)
        session.close()
        return arry

    def selectOne(self, deviceNum):
        session = DBSession()
        row = session.query(Device).filter_by(DEVICE_NUMBER=deviceNum).first()
        device = {}
        device["deviceNumber"] = row.DEVICE_NUMBER
        device["deviceRtspUrl"] = row.DEVICE_RTSPURL
        session.close()
        return device

    def updatePlateState(self, deviceNum, state):
        session = DBSession()
        deviceInfo = session.query(Device).filter_by(DEVICE_NUMBER=deviceNum).first()
        deviceInfo.DEVICE_STATE = state
        session.commit()
        session.close()
        return deviceInfo

    def insertDevice(self, devList):
        session = DBSession()
        session.query(Device).delete(synchronize_session=False)
        for dev in devList:
            session.add(Device(DEVICE_NUMBER=dev['deviceNumber'],DEVICE_NAME=dev['deviceName'],
                 DEVICE_RTSPURL=dev['deviceRtspUrl'], DEVICE_RTSPURL_SUB=dev['deviceRtspUrlSub'], DEVICE_TYPE=dev['deviceApplication'],
                 DEVICE_STATE=dev['deviceOperation'], ISDEL=0))
        session.commit()
        session.close()


class MyThread(threading.Thread):

    def run(self, sdf):
        session = DBSession()  # 每个线程都可以直接使用数据库模块定义的Session
        row = session.query(User).first()
        print(row.username + sdf)
        time.sleep(1)
        session.close()
        return row.username


def getNowTime():
    return datetime.datetime.now().strftime('%Y-%m-%d %H:%M:%S')

def getUUID():
    return str(uuid.uuid1())


if __name__ == '__main__':
    FaceBlackThread().selectById(123)
